<?php
/*
 * Data Explorer: Local Database Driver
 *      "mysqlDriver.php"
 *
 * last modified: 2/25/11
 * author: James Duran
 *
 * description: provides a mysql interface for select,
 *              update, and insert. Also provides a setup
 *              function for initially configuring select
 *              database tables.
 *
 * comments: n/a
 */

// WARNING: The $performSetup feature is not complete!!
// Executing this fuction may trigger problems!!
//
// To setup the initial database schema, refer to the .sql database schema
// in the Google project page/source tree.
//
// accessing <PROJECT_DIRECTORY>/php/mysqlDriver.php/?setup=true via the
// browser initiates a basic setup which creates the necessary mysql tables
// in your database, and populates it with initial data. 
//
// <PROJECT_DIRECTORY> - The current HTTP:// path your project resides in.
//
// NOTE: You must create the database "data_explorer" before running this function.
$performSetup = $_GET["setup"];

// mysql database connection/login information,
// update these variables to match your database configuration. 
$_mysql_db_host = "localhost";
$_mysql_db_username = "USER";
$_mysql_db_password = "PASSWORD";

// airportsInsertArray:
// @param array - php array to be inserted to db
//
// insert the contents of a php array into a mysql db.
// this function accepts array contents in the form
// followed by airports.xml
function airportsInsertArray($array) {
  foreach($array->children() as $_value) {
    $_sql = "INSERT INTO `faa_airport_loc_list` ";
    $_nameArray = explode(":", $_value->name);
    $_cityArray = explode(",", $_nameArray[0]);
    $_locArray = explode(",", $_value->geocode);

    // format the sql string relative to airports.xml
    $_sql .= "(`id`, `code`, `name`, `city`, `state`," .
    " `latitude`, `longitude`, `timezone`) ";
    $_sql .= "VALUES ";
    $_sql .= "(null, ";
    $_sql .= "'" . $_value->code . "', ";
    $_sql .= "'" . $_nameArray[1] . "', ";
    $_sql .= "'" . $_cityArray[0] . "', ";
    $_sql .= "'" . $_cityArray[1] . "', ";
    $_sql .= "" . $_locArray[1] . ", ";
    $_sql .= "" . $_locArray[0] . ", ";
    $_sql .= "'" . $_value->timezone . "')";

    // make the sql insert query, no results expected back
    sqlInsert($_sql);
    
    // the following lines are commented out, as they are for testing
    // print($_sql);
    // echo $_value->code . "<br/>"; // code
    // echo $_cityArray[0] . "<br/>"; // city
    // echo $_cityArray[1] . "<br/>"; // state
    // echo $_nameArray[1] . "<br/>"; // name
    // echo $_locArray[0] . "<br/>"; // longitude
    // echo $_locArray[1] . "<br/>"; // latitude
    // echo $_value->timezone . "<br/><br/>"; // timezone
  }
  // display finishing statement
  echo "SQL insert query completed. Check the database for any errors.";
}

function waterSitesInsertArray($array) {
  foreach($array->children() as $_value) {
    foreach($_value->children() as $_value2) {
      if($_value2->getName() == "MonitoringLocation") {
	$_site_name = $_value2->MonitoringLocationIdentity->MonitoringLocationName;
	$_site_id = $_value2->MonitoringLocationIdentity->MonitoringLocationIdentifier;
	$_huc_code = $_value2->MonitoringLocationIdentity->HUCEightDigitCode;
	$_lat = $_value2->MonitoringLocationGeospatial->LatitudeMeasure;
	$_long = $_value2->MonitoringLocationGeospatial->LongitudeMeasure;
	$_county_code = $_value2->MonitoringLocationGeospatial->CountyCode;
	$_state_code = $_value2->MonitoringLocationGeospatial->StateCode;
	$_country_code = $_value2->MonitoringLocationGeospatial->CountryCode;
	$_sql = "insert into `usgs_site_loc_list` (`id`, `site_name`, `site_id`, `huc_code`, `latitude`, `longitude`, `county_code`, `state_code`, `country_code`) values (null, '$_site_name', '$_site_id', '$_huc_code', $_lat, $_long, '$_county_code', '$_state_code', '$_country_code')";
	echo "Attempting to insert '$_site_name'<br/>";
	sqlInsert($_sql);
      }
    }
  }
  echo "The XML array has been parsed and inserted into the database. Check the database table for any error.";
}

// sqlInsert:
// @param sql - sql statement to be queried to db
//
// generic sql insert function
function sqlInsert($sql) {
  global $_mysql_db_host, $_mysql_db_username, $_mysql_db_password;
  $_con = mysql_connect($_mysql_db_host, $_mysql_db_username, $_mysql_db_password);
  if(!$_con) {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("data_explorer", $_con);
  mysql_query($sql);
  mysql_close($_con);
}

// sqlSelect:
// @param sql - sql statement to be queried to db
// @return _array - mysql result array, needs to be further parsed
//
// generic sql select * function
function sqlSelect($sql) {
  global $_mysql_db_host, $_mysql_db_username, $_mysql_db_password;
  $_con = mysql_connect($_mysql_db_host, $_mysql_db_username, $_mysql_db_password);
  if(!$_con) {
    die('Could not connect: ' . mysql_error());
  }
  mysql_select_db("data_explorer", $_con);
  $_array = mysql_query($sql);
  mysql_close($_con);
  return $_array;
}

// sqlUpdate:
//
// to be defined later
function sqlUpdate() {
}

// setup:
//
// WARNING: This function is not complete!! Executing it may cause problems!!
//
// inserts any initial content into the mysql database.
// Currently, it is expected that the database data_explorer must exist before
// any successfull insertions occur. (to be fixed later)
//
// NOTE: You will need to modify this function in order to insert initial
function setup() {
    global $_mysql_db_host, $_mysql_db_username, $_mysql_db_password;
    // load the airport xml file and insert it into the database
    $_sql = "CREATE TABLE IF NOT EXISTS `faa_airport_loc_list` (" .
    "`id` int(11) NOT NULL auto_increment," .
    "`code` text character set utf8 collate utf8_unicode_ci NOT NULL," .
    "`name` text character set utf8 collate utf8_unicode_ci NOT NULL," .
    "`city` text character set utf8 collate utf8_unicode_ci NOT NULL," .
    "`state` text character set utf8 collate utf8_unicode_ci NOT NULL," .
    "`latitude` double NOT NULL," .
    "`longitude` double NOT NULL," .
    "`timezone` text character set utf8 collate utf8_unicode_ci NOT NULL," .
    "PRIMARY KEY  (`id`));";
    $_con = mysql_connect($_mysql_db_host, $_mysql_db_username, $_mysql_db_password);
    if(!$_con) {
        die('Could not connect: ' . mysql_error());
    }
    mysql_select_db("data_explorer", $_con);
    mysql_query($_sql);
    mysql_close($_con);
    $_file_name = "usgs-xml/usgs_tx_val_verde_to_upshur_sites.xml";
    $phpArray = simplexml_load_file($_file_name);
    echo "About to import file '$_file_name'<br/>";
    waterSitesInsertArray($phpArray);
}

// WARNING: This function is not complete!! Executing it may cause problems!!
if($performSetup == "true")
  setup();
